import java.io.File;
import java.sql.*;

import ecologylab.xml.ElementState;
import ecologylab.xml.TranslationScope;
import ecologylab.xml.XMLTranslationException;

import xmlParser.ColField;
import xmlParser.Record;
import xmlParser.Table;
import xmlParser.xmlParserTranslationScope;
public class RunDB {
	Connection connection;
	   Statement statement;
	   Statement statement2;

	   public void loadDriver() throws ClassNotFoundException{
	      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
	   }

	   public void makeConnection() throws SQLException {
	      connection=
	         DriverManager.getConnection("jdbc:odbc:student");
	   }

	   public void buildStatement() throws SQLException {
	      statement = connection.createStatement();
	      statement2 = connection.createStatement();
	   }

	   public void executeQuery(GUI screen, String query) throws SQLException {

	      boolean foundResults =
	         statement.execute(query);
	      System.out.println(query);
	      if(foundResults){
	         ResultSet set = statement.getResultSet();
	         
	         statement2.execute(query);
	         ResultSet anotherset = statement2.getResultSet();
	         if(set!=null) {
	        	 displayResults(set);
	        	 displayonGUI(screen,anotherset);
	         }
	      }else {
	         connection.close();
	      }
	   }
	   
	   public void displayonGUI(GUI screen, ResultSet rs) throws SQLException {
	      ResultSetMetaData metaData = rs.getMetaData();
	      int columns=metaData.getColumnCount();
		   String resulttext = "";
		   int isfirst=1;
		   while(rs.next()){
	             if(isfirst==1) {
	            	 for(int i=1;i<=columns;++i) {
	            		 resulttext += metaData.getColumnName(i)+", ";
	            	 }
	            	 resulttext+="\n";
	            	 isfirst=0;
	             }
		         for(int i=1;i<=columns;++i) {

		        	// System.out.println(rs.getString(i));
		        	
		        	 //Without using the ecologylabFundamental lib.
//		            text+="<"+metaData.getColumnName(i)+">";
		            resulttext+=rs.getString(i)+", ";
//		             text+="</"+metaData.getColumnName(i)+">";
//		             text+="\n";
		         }
		         //Without using the ecologylabFundamental lib.
		         resulttext+="\n";
		   }
		   screen.printToScreen(resulttext);
	   }
	   

	   void displayResults(ResultSet rs) throws SQLException {
	      ResultSetMetaData metaData = rs.getMetaData();
	      int columns=metaData.getColumnCount();
	      String resulttext="";

	      //Bharat
	      Table table = new Table();
	      File sampleFile = new File("metadata.xml");
	      File sampleFile1 = new File("readfrommetadata.xml");
	      
	      while(rs.next()){
	    	  Record record = new Record();
	         for(int i=1;i<=columns;++i) {
	        	// System.out.println(rs.getString(i));
	        	
	        	 //Without using the ecologylabFundamental lib.
//	            text+="<"+metaData.getColumnName(i)+">";
//	            resulttext+=rs.getString(i)+" ";
//	             text+="</"+metaData.getColumnName(i)+">";
//	             text+="\n";
	             
	             //Bharat
	             ColField colField = new ColField();
	             colField.setName(metaData.getColumnName(i));
	             colField.setValue(rs.getString(i));
	             record.addField(colField);
	         }
	         //Bharat
	         table.addRecord(record);
	         
	         //Without using the ecologylabFundamental lib.
//	         resulttext+="\n";
	      }
	      //System.out.println("these are the results");
	      //Without using the ecologylabFundamental lib.
	      System.out.println(resulttext);
	      //System.out.println("printed");
	      
	      //Bharat
	      try {
	    	  //Writing to xmlfile metadata.xml
			table.writePrettyXML(sampleFile);
			
			//Reading from metadata.xml and writing into readfrommetadata.xml
			final TranslationScope TS = xmlParserTranslationScope.get();
			ElementState newTable = ElementState.translateFromXML(sampleFile,TS);
			newTable.writePrettyXML(sampleFile1);
			
		} catch (XMLTranslationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	   }

}
